package com.um.springboot.starter.dao;

import cn.hutool.core.util.StrUtil;
import com.um.springboot.starter.config.SystemSQL;
import com.um.springboot.starter.constants.Constants;
import com.um.springboot.starter.entity.Page;
import com.um.springboot.starter.helper.SessionHelper;
import com.um.springboot.starter.logs.SqlLog;
import com.um.springboot.starter.orm.entity.BaseEntity;
import com.um.springboot.starter.properties.HshProperties;
import com.um.springboot.starter.utils.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import javax.servlet.http.HttpServletRequest;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.concurrent.TimeUnit;

/**
 * @author 邓南辉
 * @date 2019/12/9
 * @copyright 广州优匠科技有限公司
 */
@Slf4j
public class BaseDao {

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    @Autowired
    protected NamedParameterJdbcTemplate npJdbcTemplate;

    @Autowired
    protected HshProperties hshProperties;

    @Autowired(required = false)
    protected HttpServletRequest request;

    @Autowired
    protected SessionHelper sessionHelper;

    @Autowired
    protected StringRedisTemplate redisTemplate;

    @Value("${spring.profiles.active}")
    public String PROFILE;

    @Autowired
    SqlLog sqlLog;

    /**
     * 获得ExecuteId
     * @return
     */
    protected String getExecuteId() {
        try {
            if (request != null) {
                StringUtil.getTrim(request.getAttribute(Constants.EXECUTE_ID));
            }
        } catch (Exception ignored) {
        }
        return "0";
    }

    /**
     * 获得当前用户所在公司ID
     * @return
     */
    public String getComId() {
        try {

            if(hshProperties.getSaas().getChannel().equals("session")) {
                Map sessionUser = sessionHelper.getSessionUser();
                if (sessionUser != null) {
                    return StringUtil.getTrim(sessionUser.get("com_id"));
                }
            }else if(hshProperties.getSaas().getChannel().equals("token")){
                String token = request.getHeader(hshProperties.getToken().getKey());
                String comId = redisTemplate.opsForValue().get("com_id_token_"+token);
                if (StringUtil.isEmpty(comId)) {
                    Map user = this.queryMapBySQLName(hshProperties.getSaas().getUserSqlName(), new String[]{token});
                    if (user != null) {
                        comId = StringUtil.getTrim(user.get(hshProperties.getSaas().getComIdField()));
                        redisTemplate.opsForValue().set("com_id_token_"+token,comId,1, TimeUnit.HOURS);
                        return comId;
                    }
                }else{
                    return comId;
                }
            }else if(hshProperties.getSaas().getChannel().equals("header")){
                return request.getHeader(hshProperties.getSaas().getComIdField());
            }else if(hshProperties.getSaas().getChannel().equals("domain")){
                String domain = request.getServerName();
                if(PROFILE.equals("dev")){
                    domain = hshProperties.getSaas().getDevDomain();
                }

                String comId = redisTemplate.opsForValue().get("com_id_domain_"+domain);
                if (StringUtil.isEmpty(comId)) {
                    Map item = this.queryMapBySQLName(hshProperties.getSaas().getComIdSqlName(), new String[]{domain});
                    if (item != null) {
                        comId = StringUtil.getTrim(item.get(hshProperties.getSaas().getComIdField()));
                        redisTemplate.opsForValue().set("com_id_domain_"+domain,comId,1, TimeUnit.HOURS);
                        return comId;
                    }
                }else{
                    return comId;
                }
            }
        } catch (Exception ex) {
            log.warn("", ex);
        }
        return "";
    }

    /**
     * 是否需要自动添加com_id列
     * @param tableName 忽略的表名
     * @return
     */
    public boolean isNeedComId(String tableName){
        if(hshProperties.getSaas().isEnabled()) {
            if(hshProperties.getSaas().getIgnoreTables().contains(tableName) ||
                    StrUtil.equals(hshProperties.getSaas().getGlobalComId(), getComId())){
                return false;
            }else{
                return true;
            }
        }else{
            return false;
        }
    }

    /**
     * 是否需要自动添加com_id列
     * @param tableName 忽略的表名
     * @return
     */
    public boolean isNeedSelectComId(String tableName){
        if(hshProperties.getSaas().isEnabled()) {
            if(hshProperties.getSaas().getIgnoreSelectTables().contains(tableName)){
                return false;
            }else{
                return true;
            }
        }else{
            return false;
        }
    }

    /**
     * 统一的插入方法
     * @param tableName 表名
     * @param entity 实体
     * @return
     */
    @SuppressWarnings("unchecked")
    private boolean addObject(String tableName, Object entity){
        return addObject(tableName, entity, isNeedComId(tableName));
    }

    /**
     * 统一的插入方法,返回自增ID
     * @param tableName 表名
     * @param entity 实体
     * @param isNeedComId 是否需要comid
     * @return
     */
    @SuppressWarnings("unchecked")
    private Long insertObject(String tableName, Object entity, boolean isNeedComId){
        if(isNeedComId){//云平台框架自动添加公司id
            if(entity instanceof Map) {
                ((Map) entity).put("com_id", getComId());
            }else{
                ReflectUtil.setValue(entity,"comId",this.getComId());
            }
        }

        Map<String, Object> mapValues = ColumnUtil.getInsertColumnValues(entity);

        String sql = "insert into " + tableName + " "
                + mapValues.get(ColumnUtil.COLUMN_NAMES) + " values "
                + mapValues.get(ColumnUtil.COLUMN_VALUES);

        if(hshProperties.getConfig().isRecordLog()){

            String executeId = getExecuteId();
            Map mapRecord = new Hashtable();
            mapRecord.put("sys_action_record_id", UUID.randomUUID().toString().replaceAll("-", ""));
            mapRecord.put("sys_action_log_id", executeId);
            mapRecord.put("table_name", tableName);
            mapRecord.put("action", "add");
            mapRecord.put("execute_data", JSONUtil.toJSONString(entity));
            mapRecord.put("back_data", "");
            mapRecord.put("create_date", new Date());

            this.addRecord("sys_action_record", mapRecord);
        }

        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                // 使用原始的jdbc完成preparedStatement的组建
                // RETURN_GENERATED_KEYS = 1 表示返回主键
                PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                Object[] values = (Object[])mapValues.get(ColumnUtil.ARRAY_VALUES);
                for(int index = 0;index < values.length;index ++){
                    preparedStatement.setObject(index + 1,values[index]);
                }
                return preparedStatement;
            }
        };
        // 是 KeyHolder 接口的实现类 用来接受返回的组件
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        // 执行
        this.getJdbcTemplate().update(preparedStatementCreator,generatedKeyHolder);

        // 获取生成的主键
        Long userId = generatedKeyHolder.getKey().longValue();
        return userId;
    }

    /**
     * 统一的插入方法
     * @param tableName 表名
     * @param entity 实体
     * @param isNeedComId 是否需要comid
     * @return
     */
    @SuppressWarnings("unchecked")
    private boolean addObject(String tableName, Object entity, boolean isNeedComId){
        if(isNeedComId){//云平台框架自动添加公司id
            if(entity instanceof Map) {
                ((Map) entity).put("com_id", getComId());
            }else{
                ReflectUtil.setValue(entity,"comId",this.getComId());
            }
        }

        Map<String, Object> mapValues = ColumnUtil.getInsertColumnValues(entity);

        String sql = "insert into " + tableName + " "
                + mapValues.get(ColumnUtil.COLUMN_NAMES) + " values "
                + mapValues.get(ColumnUtil.COLUMN_VALUES);

        if(hshProperties.getConfig().isRecordLog()){

            String executeId = getExecuteId();
            Map mapRecord = new Hashtable();
            mapRecord.put("sys_action_record_id", UUID.randomUUID().toString().replaceAll("-", ""));
            mapRecord.put("sys_action_log_id", executeId);
            mapRecord.put("table_name", tableName);
            mapRecord.put("action", "add");
            mapRecord.put("execute_data", JSONUtil.toJSONString(entity));
            mapRecord.put("back_data", "");
            mapRecord.put("create_date", new Date());

            this.addRecord("sys_action_record", mapRecord);
        }

        sqlLog.debug("addObject",sql,JSONUtil.toJSONString(mapValues.get(ColumnUtil.ARRAY_VALUES)));
        return this.getJdbcTemplate().update(sql,(Object[]) mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }

    /**
     * 统一的修改方法
     * @param tableName 表名
     * @param entity 实体
     * @return
     */
    @SuppressWarnings("unchecked")
    private boolean updateObject(String tableName,Object entity){
        return updateObject(tableName, entity, isNeedComId(tableName));
    }

    /**
     * 统一的修改方法
     * @param tableName 表名
     * @param entity 实体
     * @param isNeedComId 是否需要comid
     * @return
     */
    @SuppressWarnings("unchecked")
    private boolean updateObject(String tableName,Object entity, boolean isNeedComId){

        if(isNeedComId){//云平台框架自动添加公司id
            if(entity instanceof Map) {
                ((Map) entity).put("com_id", getComId());
            }else{
                ReflectUtil.setValue(entity,"comId",this.getComId());
            }
        }

        if(hshProperties.getConfig().isRecordLog()){
            String executeId = getExecuteId();

            String id = "";
            if(entity instanceof BaseEntity){
                String primaryPropertyName = NameUtils.getPropertyName(tableName+"_id");
                id = (String)ReflectUtil.getValue(entity,primaryPropertyName);
            }else{
                id = ((Map)entity).get(ColumnUtil.getPrimaryKey(tableName))+"";
            }



            Map mapBackData = this.queryMap(tableName, id);
            Map mapRecord = new Hashtable();
            mapRecord.put("sys_action_record_id",UUID.randomUUID().toString().replaceAll("-", ""));
            mapRecord.put("sys_action_log_id", executeId);
            mapRecord.put("table_name", tableName);
            mapRecord.put("action", "update");
            mapRecord.put("execute_data", JSONUtil.toJSONString(entity));
            mapRecord.put("back_data", JSONUtil.toJSONString(mapBackData));
            mapRecord.put("create_date", new Date());

            this.addRecord("sys_action_record", mapRecord);
        }



        Map<String, Object> mapValues = ColumnUtil.getUpdateColumnValues(entity,"and",new String[]{ColumnUtil.getPrimaryKey(tableName)},new String[]{});
        String sql = "update " + tableName + " set " + mapValues.get(ColumnUtil.COLUMN_NAMES)+" " + mapValues.get(ColumnUtil.WHERE_VALUES);

        sqlLog.debug("updateObject",sql,JSONUtil.toJSONString(mapValues.get(ColumnUtil.ARRAY_VALUES)));
        return getJdbcTemplate().update(sql, (Object[])mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }


    /**
     * 统一的修改方法
     * @param tableName 表名
     * @param entity 实体
     * @return
             */
    @SuppressWarnings("unchecked")
    private boolean updateObject(String tableName,Object entity,String[] updateNullProperties){



        if(isNeedComId(tableName)){//云平台框架自动添加公司id
            if(entity instanceof Map) {
                ((Map) entity).put("com_id", getComId());
            }else{
                ReflectUtil.setValue(entity,"comId",this.getComId());
            }
        }

        if(hshProperties.getConfig().isRecordLog()){
            String executeId = getExecuteId();

            String id = "";
            if(entity instanceof BaseEntity){
                String primaryPropertyName = NameUtils.getPropertyName(tableName+"_id");
                id = (String)ReflectUtil.getValue(entity,primaryPropertyName);
            }else{
                id = ((Map)entity).get(ColumnUtil.getPrimaryKey(tableName))+"";
            }



            Map mapBackData = this.queryMap(tableName, id);
            Map mapRecord = new Hashtable();
            mapRecord.put("sys_action_record_id",UUID.randomUUID().toString().replaceAll("-", ""));
            mapRecord.put("sys_action_log_id", executeId);
            mapRecord.put("table_name", tableName);
            mapRecord.put("action", "update");
            mapRecord.put("execute_data", JSONUtil.toJSONString(entity));
            mapRecord.put("back_data", JSONUtil.toJSONString(mapBackData));
            mapRecord.put("create_date", new Date());

            this.addRecord("sys_action_record", mapRecord);
        }



        Map<String, Object> mapValues = ColumnUtil.getUpdateColumnValues(entity,"and",new String[]{ColumnUtil.getPrimaryKey(tableName)},updateNullProperties);
        String sql = "update " + tableName + " set " + mapValues.get(ColumnUtil.COLUMN_NAMES)+" " + mapValues.get(ColumnUtil.WHERE_VALUES);

        sqlLog.debug("updateObject",sql,JSONUtil.toJSONString(mapValues.get(ColumnUtil.ARRAY_VALUES)));
        return getJdbcTemplate().update(sql, (Object[])mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }

    /**
     * 统一的插入方法
     * @param tableName 表名
     * @param entity 实体
     * @param properties 额外添加的属性，类型geometry专用
     * @param values 额外添加的值
     * @return
     */
    @SuppressWarnings("unchecked")
    private boolean addObject(String tableName,Object entity,String[] properties,String[] values){
        if(isNeedComId(tableName)){//云平台框架自动添加公司id
            if(entity instanceof Map) {
                ((Map) entity).put("com_id", getComId());
            }else{
                ReflectUtil.setValue(entity,"comId",this.getComId());
            }
        }

        Map<String, Object> mapValues = ColumnUtil.getInsertColumnValues(entity,properties,values);
        String sql = "insert into " + tableName + " "
                + mapValues.get(ColumnUtil.COLUMN_NAMES) + " values "
                + mapValues.get(ColumnUtil.COLUMN_VALUES);

        if(hshProperties.getConfig().isRecordLog()){
            String executeId = getExecuteId();

            Map mapRecord = new Hashtable();
            mapRecord.put("sys_action_record_id",UUID.randomUUID().toString().replaceAll("-", ""));
            mapRecord.put("sys_action_log_id", executeId);
            mapRecord.put("table_name", tableName);
            mapRecord.put("action", "add");
            mapRecord.put("execute_data", JSONUtil.toJSONString(entity));
            mapRecord.put("back_data", "");
            mapRecord.put("create_date", new Date());

            this.addRecord("sys_action_record", mapRecord);
        }

        sqlLog.debug("addObject",sql,JSONUtil.toJSONString(mapValues.get(ColumnUtil.ARRAY_VALUES)));
        return this.getJdbcTemplate().update(sql,(Object[]) mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }

    /**
     * 统一的修改方法
     * @param tableName 表名
     * @param entity 实体
     * @param properties 额外添加的属性，类型geometry专用
     * @param values 额外添加的值
     * @return
     */
    @SuppressWarnings("unchecked")
    private boolean updateObject(String tableName,Object entity,String[] properties,String[] values){

        if(isNeedComId(tableName)){//云平台框架自动添加公司id
            if(entity instanceof Map) {
                ((Map) entity).put("com_id", getComId());
            }else{
                ReflectUtil.setValue(entity,"comId",this.getComId());
            }
        }

        if(hshProperties.getConfig().isRecordLog()){
            String executeId = getExecuteId();
            String id = "";
            if(entity instanceof BaseEntity){
                String primaryPropertyName = NameUtils.getPropertyName(tableName+"_id");
                id = (String)ReflectUtil.getValue(entity,primaryPropertyName);
            }else{
                id = ((Map)entity).get(ColumnUtil.getPrimaryKey(tableName))+"";
            }

            Map mapBackData = this.queryMap(tableName, id);
            Map mapRecord = new Hashtable();
            mapRecord.put("sys_action_record_id",UUID.randomUUID().toString().replaceAll("-", ""));
            mapRecord.put("sys_action_log_id", executeId);
            mapRecord.put("table_name", tableName);
            mapRecord.put("action", "update");
            mapRecord.put("execute_data", JSONUtil.toJSONString(entity));
            mapRecord.put("back_data", JSONUtil.toJSONString(mapBackData));
            mapRecord.put("create_date", new Date());

            this.addRecord("sys_action_record", mapRecord);
        }

        Map<String, Object> mapValues = ColumnUtil.getUpdateColumnValues(entity,"and",properties,values,new String[]{ColumnUtil.getPrimaryKey(tableName)},new String[]{});
        String sql = "update " + tableName + " set " + mapValues.get(ColumnUtil.COLUMN_NAMES)+" " + mapValues.get(ColumnUtil.WHERE_VALUES);

        sqlLog.debug("updateObject",sql,JSONUtil.toJSONString(mapValues.get(ColumnUtil.ARRAY_VALUES)));
        return getJdbcTemplate().update(sql, (Object[])mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }

    @SuppressWarnings("unchecked")
    private boolean deleteObject(String tableName, Object[] properties,Object[] values){

        if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//云平台框架自动添加公司id

            Object[] newProperties = new Object[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = getComId();
            values = newValues;
        }

        String columnName = "";
        String deleteTime = DateUtil.getDateString(new Date(), "yyyy-MM-dd HH:mm:ss");
        String sql = "update " + tableName +" set is_delete = '1',delete_time = '"+deleteTime+"' ";
        int len = properties.length;
        if (len > 0)
            sql += " where ";
        for (int index = 0; index < len; index++) {
            if (index == 0)
                sql += properties[index] + " = ? ";
            else
                sql += " and " + properties[index] + " = ? ";

            columnName += " " + properties[index];
        }

        if(hshProperties.getConfig().isRecordLog()){
            List listBackData = this.queryList(tableName, properties, values);
            if(listBackData.size() > 0){
                String executeId = getExecuteId();
                Map mapRecord = new Hashtable();
                mapRecord.put("sys_action_record_id",UUID.randomUUID().toString().replaceAll("-", ""));
                mapRecord.put("sys_action_log_id", executeId);
                mapRecord.put("table_name", tableName);
                mapRecord.put("action", "delete");
                mapRecord.put("execute_data","");
                mapRecord.put("back_data", JSONUtil.toJSONString(listBackData));
                mapRecord.put("create_date", new Date());

                this.addRecord("sys_action_record", mapRecord);
            }
        }

        sqlLog.debug("deleteObject",sql,JSONUtil.toJSONString(values));
        return this.getJdbcTemplate().update(sql, values) > 0;
    }

    /**
     * 获取JdbcTemplate
     *
     * @return
     */
    protected JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    /**
     * 获取NamedParameterJdbcTemplate
     *
     * @return
     */
    protected NamedParameterJdbcTemplate getNpJdbcTemplate() {
        return npJdbcTemplate;
    }

    // ///////////////////////////////////表结构操作// 开始//////////////////////////////////////
    /**
     * 获得数据库中所有的表名
     * @return 返回一个数据库的表名列表
     */
    public List<String> getAllTableNames() {
        List<String> lstTable = new ArrayList<String>();
        try {
            DatabaseMetaData metaData = getJdbcTemplate().getDataSource()
                    .getConnection().getMetaData();
            ResultSet rs = metaData.getTables(null, null, "%", null);
            while (rs.next()) {
                lstTable.add(rs.getString(3));
            }

        } catch (SQLException e) {
            log.error("", e);
        }
        return lstTable;
    }

    /**
     * 获得一个表中所有列名
     * @param tableName 表名
     * @return 返回表的列名列表
     */
    public List<String> getColumnNames(String tableName) {
        List<String> list = new ArrayList<String>();

        try {
            SqlRowSet rowSet = this.getJdbcTemplate().queryForRowSet(
                    "select * from " + tableName);
            SqlRowSetMetaData rowData = rowSet.getMetaData();
            for (int index = 1; index <= rowData.getColumnCount(); index++) {
                list.add(rowData.getColumnName(index));
            }
        } catch (Exception e) {
            log.error("get getColumnNames fail!", e);
        }
        return list;
    }

    /**
     * 获得一个表中所有的列名与数据类型的键值对
     * @param tableName 表名
     * @return 返回列名做为键，列类型作为值的map
     */
    public Map<String, Object> getColumnNamesMap(String tableName) {
        Map<String, Object> mapColumns = new HashMap<String, Object>();
        try {
            String keyName = "";
            int typeName = 0;
            SqlRowSet rowSet = this.getJdbcTemplate().queryForRowSet(
                    "select * from " + tableName);
            SqlRowSetMetaData rowDate = rowSet.getMetaData();
            for (int index = 1; index <= rowDate.getColumnCount(); index++) {
                keyName = rowDate.getColumnName(index);
                typeName = rowDate.getColumnType(index);
                mapColumns.put(keyName, typeName);
            }

        } catch (Exception e) {
            log.error("get columnNamesMap fail!", e);
        }
        return mapColumns;
    }

    // ///////////////////////////////////保存更改操作 开始//////////////////////////////////////

    /**
     * 添加记录，饶开框架，不做历史记录
     */
    public boolean addRecord(String tableName, Object entity){
        if(isNeedComId(tableName)){//云平台框架自动添加公司id
            if(entity instanceof Map) {
                ((Map) entity).put("com_id", getComId());
            }else{
                ReflectUtil.setValue(entity,"comId",this.getComId());
            }
        }

        Map<String, Object> mapValues = ColumnUtil.getInsertColumnValues(entity);
        String sql = "insert into " + tableName + " "
                + mapValues.get(ColumnUtil.COLUMN_NAMES) + " values "
                + mapValues.get(ColumnUtil.COLUMN_VALUES);

        sqlLog.debug("addRecord",sql,mapValues.get(ColumnUtil.ARRAY_VALUES));
        return this.getJdbcTemplate().update(sql,(Object[]) mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }

    /**
     * 添加记录，饶开框架，不做历史记录
     */
    public boolean updateRecord(String tableName, Object entity){
        if(isNeedComId(tableName)){//云平台框架自动添加公司id
            if(entity instanceof Map) {
                if(!((Map) entity).containsKey("com_id")){
                    ((Map) entity).put("com_id", getComId());
                }
            }else{
                ReflectUtil.setValue(entity,"comId",this.getComId());
            }
        }

        Map<String, Object> mapValues = ColumnUtil.getUpdateColumnValues(entity,"and",new String[]{ColumnUtil.getPrimaryKey(tableName)},new String[]{});
        String sql = "update " + tableName + " set " + mapValues.get(ColumnUtil.COLUMN_NAMES)+" " + mapValues.get(ColumnUtil.WHERE_VALUES);

        sqlLog.debug("updateRecord",sql,mapValues.get(ColumnUtil.ARRAY_VALUES));
        return getJdbcTemplate().update(sql, (Object[])mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }

    /**
     * 添加记录，饶开框架，不添加com_id
     * @param tableName 表名
     * @param entity 实体
     * @return 保存成功返回true,否则返回false
     */
    public boolean addRow(String tableName, Object entity){

        Map<String, Object> mapValues = ColumnUtil.getInsertColumnValues(entity);
        String sql = "insert into " + tableName + " "
                + mapValues.get(ColumnUtil.COLUMN_NAMES) + " values "
                + mapValues.get(ColumnUtil.COLUMN_VALUES);

        sqlLog.debug("addRow",sql,mapValues.get(ColumnUtil.ARRAY_VALUES));
        return this.getJdbcTemplate().update(sql,(Object[]) mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }

    /**
     * 更改记录，不添加com_id
     * @param tableName 表名
     * @param entity 实体
     * @return 保存成功返回true,否则返回false
     */
    public boolean updateRow(String tableName, Object entity){

        Map<String, Object> mapValues = ColumnUtil.getUpdateColumnValues(entity,"and",new String[]{ColumnUtil.getPrimaryKey(tableName)},new String[]{});
        String sql = "update " + tableName + " set " + mapValues.get(ColumnUtil.COLUMN_NAMES)+" " + mapValues.get(ColumnUtil.WHERE_VALUES);

        sqlLog.debug("updateRow",sql,mapValues.get(ColumnUtil.ARRAY_VALUES));
        return getJdbcTemplate().update(sql, (Object[])mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }

    /**
     * 删除记录，不添加com_id
     * @param tableName 表名
     * @param properties 属性名
     * @param values 值
     * @return 保存成功返回true,否则返回false
     */
    public boolean deleteRow(String tableName, String[] properties, Object[] values){

        String columnName = "";
        String deleteTime = DateUtil.getDateString(new Date(), "yyyy-MM-dd HH:mm:ss");
        String sql = "update " + tableName +" set is_delete = '1',delete_time = '"+deleteTime+"' ";
        int len = properties.length;
        if (len > 0)
            sql += " where ";
        for (int index = 0; index < len; index++) {
            if (index == 0)
                sql += properties[index] + " = ? ";
            else
                sql += " and " + properties[index] + " = ? ";

            columnName += " " + properties[index];
        }
        sqlLog.debug("deleteRow",sql,JSONUtil.toJSONString(values));
        return this.getJdbcTemplate().update(sql, values) > 0;
    }

    /**
     * 查询一个实体(内部使用，service不要使用)，饶开框架，不做历史记录
     * @param tableName 表名
     * @param properties 属性名数组
     * @param values 属性值数组
     * @return 返回一个对象Map
     */
    public Map queryRow(String tableName,Object[] properties,Object[] values){

        String sql = "select * from " + tableName + " where is_delete = '0' ";
        int len = properties.length;
        for (int index = 0; index < len; index++) {
            sql += " and " + properties[index] +" = ? ";
        }

        sqlLog.debug("queryRow",sql,values);
        List list = this.getJdbcTemplate().queryForList(sql, values);
        if(list != null && list.size() > 0){
            return (Map)list.get(0);
        }else{
            return null;
        }
    }

    /**
     * 查询一个列表(内部使用，service不要使用)，饶开框架，不做历史记录
     * @param tableName 表名
     * @param properties 属性名数组
     * @param values 属性值数组
     * @return 返回一个对象List
     */
    public List queryRows(String tableName,Object[] properties,Object[] values){

        String sql = "select * from " + tableName + " where is_delete = '0' ";
        int len = properties.length;
        for (int index = 0; index < len; index++) {
            sql += " and " + properties[index] +" = ? ";
        }

        sqlLog.debug("queryRows",sql,values);
        List list = this.getJdbcTemplate().queryForList(sql, values);
        return list;
    }

    /**
     * 查询一个列表(内部使用，service不要使用)，饶开框架，不做历史记录
     * @param tableName 表名
     * @param properties 属性名数组
     * @param values 属性值数组
     * @param num 返回列表数量
     * @return 返回一个对象List
     */
    public List queryRowsNum(String tableName,Object[] properties,Object[] values,Object[] orderNames,Object[] sortOns,int num){

        String sql = "select * from " + tableName + " where is_delete = '0' ";
        int len = properties.length;
        for (int index = 0; index < len; index++) {
            sql += " and " + properties[index] +" = ? ";
        }

        if(orderNames.length > 0){
            int start=0;
            for(int index = 0;index < orderNames.length;index ++){
                Object property = orderNames[index];
                if(property==null||property.equals(""))
                    continue;
                if(start==0)
                    sql += " order by ";
                if(start > 0)
                    sql += " , ";
                sql += property +" "+sortOns[index];
                start++;
            }
        }

        sql += " limit 0,"+num;
        sqlLog.debug("queryRowsNum",sql,values,num);
        List list = this.getJdbcTemplate().queryForList(sql, values);
        return list;
    }

    /**
     * 查询一个列表(内部使用，service不要使用)，饶开框架，不做历史记录
     * @param tableName 表名
     * @param properties 属性名数组
     * @param values 属性值数组
     * @param num 返回列表数量
     * @return 返回一个对象List
     */
    public List queryRowsNum(String tableName,Object[] properties,Object[] values,int num){

        String sql = "select * from " + tableName + " where is_delete = '0' ";
        int len = properties.length;
        for (int index = 0; index < len; index++) {
            sql += " and " + properties[index] +" = ? ";
        }

        sql += " limit 0,"+num;
        sqlLog.debug("queryRowsNum",sql,values,num);
        List list = this.getJdbcTemplate().queryForList(sql, values);
        return list;
    }

    /**
     * 执行sql语句
     * @param sql
     * @return
     */
    public boolean executeSQL(String sql,Object[] values){

        sqlLog.debug("executeSQL",sql,values);
        return getJdbcTemplate().update(sql,values) > 0;
    }

    /**
     * 执行sql语句（返回受影响数量）
     * @param sql
     * @return
     */
    public int executeSQLCount(String sql, Object[] values) {
        sqlLog.debug("executeSQL", sql, values);
        return getJdbcTemplate().update(sql, values);
    }

    /**
     * 插入一条记录
     * @param tableName 表名
     * @param entity 列名和值
     * @return 执行成功返回true
     */
    public Long insert(String tableName, Object entity) {
        return this.insertObject(tableName, entity,isNeedComId(tableName));
    }

    /**
     * 插入一条记录
     * @param tableName 表名
     * @param entity 列名和值
     * @return 执行成功返回true
     */
    public boolean add(String tableName, Object entity) {
        return this.addObject(tableName, entity);
    }

    /**
     * 插入一条记录
     * @param tableName 表名
     * @param entity 列名和值
     * @param isNeedComId 是否需要comid
     * @return 执行成功返回true
     */
    public boolean add(String tableName, Object entity, boolean isNeedComId) {
        return this.addObject(tableName, entity, isNeedComId);
    }

    /**
     * 插入一条记录
     * @param tableName 表名
     * @param entity 列名和值
     * @param properties 额外添加的属性，类型geometry专用
     * @param values 额外添加的值
     * @return 执行成功返回true
     */
    public boolean add(String tableName, Object entity,String[] properties,String[] values) {
        return this.addObject(tableName, entity,properties,values);
    }

    /**
     * 保存一个对象列表
     * @param tableName 表名
     * @param list 多个对象的列表
     * @return 执行成功返回true
     *
     */
    public boolean addEntityList(String tableName,List<Object> list){
        for(Object entity : list){
            String id = UUID.randomUUID().toString().replaceAll("-", "");

            if(entity instanceof BaseEntity){
                String primaryPropertyName = NameUtils.getPropertyName(tableName+"_id");
                ReflectUtil.setValue(entity,primaryPropertyName,id);
            }
            boolean result = addObject(tableName,entity);
            if(result == false){
                return false;
            }
        }
        return true;
    }

    /**
     * 保存一个对象列表
     * @param tableName 表名
     * @param list 多个对象的列表
     * @return 执行成功返回true
     *
     */
    public boolean addList(String tableName,List<Map> list){
        for(Object entity : list){
            String id = UUID.randomUUID().toString().replaceAll("-", "");

            ((Map)entity).put(ColumnUtil.getPrimaryKey(tableName), id);
            boolean result = addObject(tableName,entity);
            if(result == false){
                return false;
            }
        }
        return true;
    }

    /**
     * 自动根据主健来修改 一条记录
     * @param tableName 表名
     * @param entity 属性名和值
     * @return 执行成功返回true
     */
    public boolean update(String tableName,Object entity,String[] updateNullProperties){
        return this.updateObject(tableName, entity,updateNullProperties);
    }

    /**
     * 自动根据主健来修改 一条记录
     * @param tableName 表名
     * @param entity 属性名和值
     * @return 执行成功返回true
     */
    public boolean update(String tableName,Object entity){
        return this.updateObject(tableName, entity);
    }

    /**
     * 自动根据主健来修改 一条记录
     * @param tableName 表名
     * @param entity 属性名和值
     * @param isNeedComId 是否需要comid
     * @return 执行成功返回true
     */
    public boolean update(String tableName,Object entity, boolean isNeedComId){
        return this.updateObject(tableName, entity, isNeedComId);
    }

    /**
     * 自动根据主健来修改 一条记录
     * @param tableName 表名
     * @param entity 属性名和值
     * @param properties 额外添加的属性，类型geometry专用
     * @param values 额外添加的值
     * @return 执行成功返回true
     */
    public boolean update(String tableName,Object entity,String[] properties,String[] values){
        return this.updateObject(tableName, entity,properties,values);
    }

    /**
     * 根据SQL执行update
     *
     * @param sql    SQL语句
     * @param values SQL语句的参数值
     * @return 返回受影响条数
     */
    public int updateBySQL(String sql, Object[] values) {
        sqlLog.debug("updateBySQL", sql, values);
        return this.getJdbcTemplate().update(sql, values);
    }

    /**
     * 根据SQL名称执行update
     *
     * @param sqlName SQL名称
     * @param values  SQL语句的参数值
     * @return 返回受影响条数
     */
    public int updateBySQLName(String sqlName, Object[] values) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        sqlLog.debug("updateBySQLName", sqlName);
        return this.updateBySQL(sql, values);
    }

    /**
     * 根据指定属性来修改 一条记录
     * @param tableName 表名
     * @param map 属性名和值
     * @param oper =或like
     * @param properties 属性名数组
     * @return 执行成功返回true

    public boolean update(String tableName,Map<String,Object> map,String oper,String[] properties){
    Map<String, Object> mapValues = ColumnUtil.getUpdateColumnValues(map,oper,properties);
    String sql = "update " + tableName + " set " + mapValues.get(ColumnUtil.COLUMN_NAMES)+" " + mapValues.get(ColumnUtil.WHERE_VALUES);
    return getJdbcTemplate().update(sql, (Object[])mapValues.get(ColumnUtil.ARRAY_VALUES)) > 0;
    }	*/

    /**
     * 根据指定属性来修改一条记录的部分字段
     * @param tableName 表名
     * @param updateColumns 需要修改的属性
     * @param updateValues 需要修改的值
     * @param properties 属性名数组(条件)
     * @param values 属性值数组(条件)
     * @param oper =或like
     * @return 执行成功返回true

    public boolean update(String tableName,String[] updateColumns,Object[] updateValues,
    String[] properties,Object[] values,String oper){

    List<String> updateColumnsList = Arrays.asList(updateColumns);
    List<String> propertiesList = Arrays.asList(properties);
    List<Object> updateValueList = Arrays.asList(updateValues);
    List<Object> valuesList = Arrays.asList(values);
    return this.update(tableName, updateColumnsList, updateValueList, propertiesList, valuesList, oper);
    }*/

    /**
     * 根据指定属性来修改一条记录的部分字段
     * @param tableName 表名
     * @param updateColumns 需要修改的属性
     * @param updateValues 需要修改的值
     * @param properties 属性名数组(条件)
     * @param values 属性值数组(条件)
     * @param oper =或like
     * @return 执行成功返回true

    public boolean update(String tableName,List<String> updateColumns,List<Object> updateValues,
    List<String> properties,List<Object> values,String oper){
    Map<String, Object> mapValues = ColumnUtil.getUpdateColumnValues(updateColumns,properties,oper);
    List<Object> allList = new ArrayList<Object>();//合并List
    allList.addAll(updateValues);
    allList.addAll(values);
    String sql = "update " + tableName + " set " + mapValues.get(ColumnUtil.COLUMN_NAMES)+" " + mapValues.get(ColumnUtil.WHERE_NAMES);
    return getJdbcTemplate().update(sql, allList.toArray()) > 0;
    }*/

    /////////////////////////////////////删除操作 开始//////////////////////////////////////
    /**
     * 根据多个属性进行逻辑删除
     * @param tableName 表名
     * @param properties 属性名称数组
     * @param values 属性值数组
     * @return 执行成功返回true
     */
    public boolean delete(String tableName, Object[] properties,Object[] values) {
        return this.deleteObject(tableName, properties, values);
    }

    /**
     * 根据一个属性进行逻辑删除
     * @param tableName 表名
     * @param property 属性名称
     * @param value 值
     * @return 执行成功返回true
     */
    public boolean delete(String tableName, String property, Object value) {
        return this.delete(tableName, new Object[]{property}, new Object[]{value});
    }

    /**
     * 根据一个属性物理删除
     * @param tableName 表名
     * @param columnName 属性名称
     * @param value 值
     * @return 执行成功返回true
     */
    @SuppressWarnings("unchecked")
    public boolean deleteTrue(String tableName, String columnName, Object value) {

        String sql = "delete from " + tableName + " where " + columnName + " = ? ";
        if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//云平台框架自动添加公司id
            sql += " and com_id = '"+getComId()+"'";
        }
        return this.getJdbcTemplate().update(sql, new Object[]{value}) > 0;
    }

    /**
     * 根据一个属性物理删除
     * @param tableName 表名
     * @param properties 属性名称列表
     * @param values 值列表
     * @return 执行成功返回true
     */
    @SuppressWarnings("unchecked")
    public boolean deleteTrue(String tableName, String[] properties, Object[] values) {

        if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = getComId();
            values = newValues;
        }

        String sql = "delete from " + tableName;
        int len = properties.length;
        if (len > 0)
            sql += " where ";
        for (int index = 0; index < len; index++) {
            if (index == 0)
                sql += properties[index] + " = ? ";
            else
                sql += " and " + properties[index] + " = ? ";
        }

        sqlLog.debug("deleteTrue",sql,values);

        return this.getJdbcTemplate().update(sql, values) > 0;
    }

    /**
     * 递归逻辑删除一个树桔构的记录以及它的所有子节点
     * @param tableName 表名
     * @param list 主键ID列表
     * @param parentColumn 父节点列名
     * @return 执行成功返回true
     */
    @SuppressWarnings("unchecked")
    public boolean deleteNode(String tableName,List<String> list ,String parentColumn){
        for(Object id:list){
            List<Map<String,Object>> queryList = this.queryList(tableName,new String[]{ColumnUtil.getPrimaryKey(tableName)}, new Object[]{parentColumn}, new Object[]{id},new Object[]{},new Object[]{});
            List<String> childList = new ArrayList<>();
            for(Map<String,Object> map:queryList){
                childList.add(map.get(ColumnUtil.getPrimaryKey(tableName)).toString());
            }
            if(childList.size()>0){
                deleteNode(tableName,childList,parentColumn);
            }
            boolean result = this.delete(tableName, id.toString());
            if(result == false){
                return false;
            }
        }
        return true;
    }

    /**
     * 递归逻辑删除一个树桔构的记录以及它的所有子节点
     * @param tableName 表名
     * @param returnList 所有子节点的对象
     * @param list 主键ID列表
     * @param parentColumn 父节点列名
     * @return
     */
    @SuppressWarnings("unchecked")
    public void deleteNode(String tableName,List returnList,List<String> list ,String parentColumn){
        for(Object id:list){
            //returnList.addAll(list);
            List<Map<String,Object>> queryList = this.queryList(tableName,new String[]{ColumnUtil.getPrimaryKey(tableName)}, new Object[]{parentColumn}, new Object[]{id},new Object[]{},new Object[]{});
            List<String> childList = new ArrayList<>();
            for(Map<String,Object> map:queryList){
                childList.add(map.get(ColumnUtil.getPrimaryKey(tableName)).toString());
            }
            if(childList.size()>0){
                deleteNode(tableName,returnList,childList,parentColumn);
            }
            returnList.add(this.queryMap(tableName, id));
            this.delete(tableName, id.toString());

        }
    }

    /**
     *  逻辑删除多条记录
     * @param tableName 表名
     * @param list 主键ID列表
     * @return 执行成功返回true
     */
    public boolean delete(String tableName,List<String> list){
        for(Object id : list){
            boolean result = delete(tableName,ColumnUtil.getPrimaryKey(tableName),id);
            if(result == false){
                return false;
            }
        }
        return true;
    }

    /**
     * 逻辑删除一个列表及对应关系子表
     * @param tableName 主表名
     * @param foreignTable 外键表名
     * @param list 主表ID列表
     * @return 执行成功返回true
     */
    public boolean delete(String tableName,String foreignTable,List<Object> list){
        for(Object id : list){
            String columnName = ColumnUtil.getPrimaryKey(tableName);
            boolean result = delete(foreignTable,columnName,id);
            result = result && delete(tableName,columnName,id);
            if(result == false){
                return false;
            }
        }
        return true;
    }

    /**
     * 逻辑删除一个列表及对应关系子表
     * @param tableName 主表名
     * @param foreignTable 外键表名
     * @param column 删除条件的列名
     * @param list 删除条件的列对应的值列表
     * @return 执行成功返回true
     */
    public boolean delete(String tableName,String foreignTable,String column,List<Object> list){
        for(Object id : list){
            String columnName = ColumnUtil.getPrimaryKey(tableName);
            boolean result = delete(foreignTable,column,id);
            result = result && delete(tableName,columnName,id);
            if(result == false){
                return false;
            }
        }
        return true;
    }

    /**
     * 根据主键ID逻辑删除一条记录
     * @param tableName 表名
     * @param id 主键ID
     */
    public boolean delete(String tableName,String id){
        return delete(tableName,ColumnUtil.getPrimaryKey(tableName),id);
    }

    // ///////////////////////////////////查询操作 开始//////////////////////////////////////
    /**
     * 根据sql查询一个列表
     * @param sql SQL语句
     * @param values SQL语句的参数值
     * @return 返回一个List
     */
    @SuppressWarnings("unchecked")
    public List queryListBySQL(String sql,Object[] values){
        sqlLog.debug("queryListBySQL",sql,values);
        return this.getJdbcTemplate().queryForList(sql,values);
    }

    /**
     * 根据sql查询一个列表
     * @param sql SQL语句
     * @param values SQL语句的命名参数值
     * @return 返回一个List
     */
    @SuppressWarnings("unchecked")
    public List queryListByNpSQL(String sql, Map<String, ?> values) {
        sqlLog.debug("queryListByNpSQL", sql, values);
        return this.getNpJdbcTemplate().queryForList(sql, values);
    }

    /**
     * 根据sql查询一个列表
     * @param sqlName SQL名称
     * @param values SQL语句的参数值
     * @return 返回一个List
     */
    @SuppressWarnings("unchecked")
    public List queryListBySQLName(String sqlName,Object[] values){
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        sqlLog.debug("queryListBySQLName", sqlName);
        return this.queryListBySQL(sql, values);
    }

    /**
     * 根据sql查询一个列表
     * @param sqlName SQL名称
     * @param values SQL语句的命名参数值
     * @return 返回一个List
     */
    @SuppressWarnings("unchecked")
    public List queryListByNpSQLName(String sqlName, Map<String, ?> values) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        sqlLog.debug("queryListBySQLName", sqlName);
        return this.queryListByNpSQL(sql, values);
    }

    /**
     * 根据sql查询一个列表
     * @param sqlName SQL名称
     * @param replaceStr SQL语句中替换[condition]
     * @param list SQL语句查询条件的参数值
     * @return 返回一个List
     */
    public List queryListBySQLNameReplace(String sqlName, String replaceStr, Object[] list) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName).replace("[condition]", replaceStr);
        sqlLog.debug("queryListBySQLNameReplace", sqlName, replaceStr);
        return this.queryListBySQL(sql, list);
    }

    /**
     * 查询表所有的数据
     * @param tableName 表名
     * @return 返回表所有数据的List
     */
    @SuppressWarnings("unchecked")
    public List queryList(String tableName) {
        String sql = "select * from " + tableName +" where is_delete = '0' ";
        if(isNeedComId(tableName) && isNeedSelectComId(tableName)) {//自动添加查询条件com_id=xxx
            sql += " and com_id = '"+getComId()+"'";
        }
        sqlLog.debug("queryList",sql,new Object[]{});
        List list = this.getJdbcTemplate().queryForList(sql);
        return list;
    }

    /**
     * 根据多个属性查询指定属性的列表
     * @param tableName 表名
     * @param columns 返回数据的列名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @param orderNames 排序列名
     * @param sortOns asc或desc
     * @return 返回符合条件的列表List
     */
    @SuppressWarnings("unchecked")
    protected List query(String tableName,Object[] columns,Object[] properties,Object[] values,Object[] orderNames,Object[] sortOns,String oper) {
        if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = getComId();
            values = newValues;
        }

        String sql = "select * from " + tableName + " where is_delete = '0' ";
        if(columns.length > 0)
            sql = "select "+ColumnUtil.getColumns(columns)+" from " + tableName  + " where is_delete = '0' ";
        int len = properties.length;
        for (int index = 0; index < len; index++) {
            if(!ColumnUtil.isSqlPropertyNameValid(properties[index]+""))
                continue;

            if ("like".equalsIgnoreCase(oper) && !StringUtil.getTrim(values[index]).contains("%")) {
                sql += " and " + properties[index] + " = ? ";
            } else {
                sql += " and " + properties[index] + " " + oper + " ? ";
            }
        }

        if(orderNames.length > 0){
            int start=0;
            for(int index = 0;index < orderNames.length;index ++){
                Object property = orderNames[index];
                if(property==null||property.equals(""))
                    continue;
                if(start==0)
                    sql += " order by ";
                if(start > 0)
                    sql += " , ";
                sql += property +" "+sortOns[index];
                start++;
            }
        }
        sqlLog.debug("query",sql,values);
        List list = this.getJdbcTemplate().queryForList(sql, values);
        return list;
    }

    public List queryList(String sqlName,Map map,Map custom,List list) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);

        Map mapQuery = ColumnUtil.getQueryCondition(map);
        String condition = StringUtil.getTrim(mapQuery.get("sql"));

        Map mapCustom = ColumnUtil.getQueryCustom(custom);

        sql = sql.replace("[custom]", StringUtil.getTrim(mapCustom.get("sql")));
        if(!condition.equals("")){
            condition = " 1=1 "+condition;
        }else{
            condition = " 1=1 ";
        }
        sql = sql.replace("[condition]", condition);

        list.addAll((List)mapQuery.get("list"));
        list.addAll((List)mapCustom.get("list"));

        sqlLog.debug("queryList",sql,list);
        List<Map<String, Object>> lst = getJdbcTemplate().queryForList(sql,list.toArray());
        return lst;
    }

    public List queryList(String sqlName,Map map,Map custom,List list,int start,int end) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);

        Map mapQuery = ColumnUtil.getQueryCondition(map);
        String condition = StringUtil.getTrim(mapQuery.get("sql"));

        Map mapCustom = ColumnUtil.getQueryCustom(custom);

        sql = sql.replace("[custom]", StringUtil.getTrim(mapCustom.get("sql")));
        if(!condition.equals("")){
            condition = " 1=1 "+condition;
        }else{
            condition = " 1=1 ";
        }
        sql = sql.replace("[condition]", condition);


        list.addAll((List)mapQuery.get("list"));
        list.addAll((List)mapCustom.get("list"));
        list.add(start);
        list.add(end);

        sqlLog.debug("queryList",sql,list);
        List<Map<String, Object>> lst = getJdbcTemplate().queryForList(sql,list.toArray());
        return lst;
    }


    /**
     * 根据多个属性精确查询指定列表
     * @param tableName 表名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @return 返回符合条件的列表List
     */
    @SuppressWarnings("unchecked")
    public List queryList(String tableName,Object[] properties,Object[] values) {
        return query(tableName,new Object[]{},properties,values,new Object[]{},new Object[]{},"=");
    }

    /**
     * 根据多个属性精确查询指定列表
     * @param tableName 表名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @param orderNames 排序字段列表
     * @param sortOns 降序或升序列表 asc或desc
     * @return 返回符合条件的列表List
     */
    @SuppressWarnings("unchecked")
    public List queryList(String tableName,Object[] properties,Object[] values,Object[] orderNames,Object[] sortOns) {
        return query(tableName,new Object[]{},properties,values,orderNames,sortOns,"=");
    }

    /**
     * 根据多个属性精确查询列表,可以指定查询指定的列
     * @param tableName 表名
     * @param columns 返回的列名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @param orderNames 排序字段列表
     * @param sortOns 降序或升序列表 asc或desc
     * @return 返回符合条件的列表List
     */
    @SuppressWarnings("unchecked")
    public List queryList(String tableName,Object[] columns,Object[] properties,Object[] values,Object[] orderNames,Object[] sortOns) {
        return query(tableName,columns,properties,values,orderNames,sortOns,"=");
    }

    /**
     * 根据多个属性模糊查询指定列表
     * @param tableName 表名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @return 返回符合条件的列表List
     */
    @SuppressWarnings("unchecked")
    public List queryLikeList(String tableName,Object[] properties,Object[] values) {
        return query(tableName,new Object[]{},properties,values,new Object[]{},new Object[]{},"like");
    }

    /**
     * 根据多个属性模糊查询指定列表
     * @param tableName 表名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @param orderNames 排序字段列表
     * @param sortOns 降序或升序列表 asc或desc
     * @return 返回符合条件的列表List
     */
    @SuppressWarnings("unchecked")
    public List queryLikeList(String tableName,Object[] properties,Object[] values,Object[] orderNames,Object[] sortOns) {
        return query(tableName,new Object[]{},properties,values,orderNames,sortOns,"like");
    }

    /**
     * 根据多个属性模糊查询列表,可以指定查询指定的列
     * @param tableName 表名
     * @param columns 返回的列名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @param orderNames 排序字段列表
     * @param sortOns 降序或升序列表 asc或desc
     * @return 返回符合条件的列表List
     */
    @SuppressWarnings("unchecked")
    public List queryLikeList(String tableName,Object[] columns,Object[] properties,Object[] values,Object[] orderNames,Object[] sortOns) {
        return query(tableName,columns,properties,values,orderNames,sortOns,"like");
    }

    /**
     * 查询符合条件，指定返回条数的列表
     * @param tableName 表名
     * @param properties 查询属性名
     * @param values 要询属性值
     * @param orderNames 排序名称
     * @param sortOns asc或desc
     * @param num 查询的记录条数
     * @return 返回指定大小列表的List
     */
    @SuppressWarnings("unchecked")
    public List queryNum(String tableName,Object[] properties,Object[] values,Object[] orderNames,Object[] sortOns,int num) {
        if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = getComId();
            values = newValues;
        }

        String sql = "select * from " + tableName + " where is_delete = '0' ";
        int len = properties.length;
        for (int index = 0; index < len; index++) {
            sql += " and " + properties[index] +" = ? ";
        }
        if(orderNames.length > 0){
            int start=0;
            for(int index = 0;index < orderNames.length;index ++){
                Object property = orderNames[index];
                if(property==null||property.equals(""))
                    continue;
                if(start==0)
                    sql += " order by ";
                if(start > 0)
                    sql += " , ";
                sql += property +" "+sortOns[index];
                start++;
            }
        }
        sql += " limit 0,"+num;
        sqlLog.debug("queryNum",sql,values,num);
        List list = this.getJdbcTemplate().queryForList(sql, values);
        return list;
    }



    /**
     * 根据多个属性查询指定属性的列表
     * @param tableName 表名
     * @param ignoreCcolumns 不需要返回的列名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @param orderNames 排序字段
     * @param sortOns 降序或升序
     * @return 返回列表List
     */
    @SuppressWarnings("unchecked")
    public List queryIgnore(String tableName,Object[] ignoreCcolumns,Object[] properties,Object[] values,Object[] orderNames,Object[] sortOns,String oper) {
        List list = null;
        try {

            if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//云平台框架自动添加公司id

                String[] newProperties = new String[properties.length + 1];
                System.arraycopy(properties,0,newProperties,0,properties.length);
                newProperties[newProperties.length-1] = "com_id";
                properties = newProperties;

                Object[] newValues = new Object[values.length + 1];
                System.arraycopy(values,0,newValues,0,values.length);
                newValues[newValues.length-1] = getComId();
                values = newValues;
            }

            String sql = "select * from " + tableName  + " where is_delete = '0' ";
            if(ignoreCcolumns.length > 0){
                sql = "select "+ColumnUtil.getIgnoreColumns(tableName,ignoreCcolumns)+" from " + tableName+ " where is_delete = '0' ";
            }

            int len = properties.length;

            for (int index = 0; index < len; index++) {
                if ("like".equalsIgnoreCase(oper) && !StringUtil.getTrim(values[index]).contains("%")) {
                    sql += " and " + properties[index] + " = ? ";
                } else {
                    sql += " and " + properties[index] + " " + oper + " ? ";
                }
            }

            if(orderNames.length > 0){
                int start=0;
                for(int index = 0;index < orderNames.length;index ++){
                    Object property = orderNames[index];
                    if(property==null||property.equals(""))
                        continue;
                    if(start==0)
                        sql += " order by ";
                    if(index > 0)
                        sql += " , ";
                    sql += property +" "+sortOns[index];
                    start++;
                }
            }
            sqlLog.debug("queryIgnore",sql,values);
            list = this.getJdbcTemplate().queryForList(sql, values);
        } catch (Exception e) {
            log.error("select " + tableName + " by  fail!", e);
        }
        return list;
    }

    ///////////////////////////////////////查询一个对象 map//////////////////////////////////
    /**
     * 根据多个属性查询一个对象，可以指定返回的列
     * @param tableName 表名
     * @param columns 返回的列名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @return 返回一个对象
     */
    @SuppressWarnings("unchecked")
    public Map queryMap(String tableName,Object[] columns,Object[] properties,Object[] values,String oper) {

        if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = getComId();
            values = newValues;
        }


        String sql = "select * from " + tableName + " where is_delete = '0' ";
        if(columns.length > 0)
            sql = "select "+ColumnUtil.getColumns(columns)+" from " + tableName + " where is_delete = '0' ";

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            if ("like".equalsIgnoreCase(oper) && !StringUtil.getTrim(values[index]).contains("%")) {
                sql += " and " + properties[index] + " = ? ";
            } else {
                sql += " and " + properties[index] + " " + oper + " ? ";
            }
        }
        sqlLog.debug("queryMap",sql,values);
        List list = this.getJdbcTemplate().queryForList(sql, values);
        if(list.size() > 0)
            return (Map)list.get(0);
        else
            return null;
    }

    /**
     * 根据多个属性精确查询一个对象，可以指定返回的列
     * @param tableName 表名
     * @param columns 返回的列名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @return 返回一个对象
     */
    @SuppressWarnings("unchecked")
    public Map queryMap(String tableName,Object[] columns, Object[] properties,Object[] values) {
        return queryMap(tableName,columns,properties,values,"=");
    }

    /**
     * 根据一个属性精确查询一个对象
     * @param tableName 表名
     * @param property 查询属性名称
     * @param value 查询属性值
     * @return 返回一个对象
     */
    @SuppressWarnings("unchecked")
    public Map queryMap(String tableName,String property,Object value) {
        return queryMap(tableName,new Object[]{},new Object[]{property},new Object[]{value},"=");
    }

    /**
     * 根据多个属性精确查询一个对象
     * @param tableName 表名
     * @param properties 查询属性名称
     * @param values 查询属性值
     * @return 返回一个对象
     */
    @SuppressWarnings("unchecked")
    public Map queryMap(String tableName, Object[] properties,Object[] values) {
        return queryMap(tableName,new Object[]{},properties,values,"=");
    }

    /**
     * 根据SQL语句查询一个记录
     * @param sql SQL语句
     * @param list SQL语句需要的参数值
     * @return 返回一个对象
     */
    @SuppressWarnings("unchecked")
    public Map queryMapBySQL(String sql, Object[] list) {
        sqlLog.debug("queryMapBySQL",sql,list);
        List lst = this.getJdbcTemplate().queryForList(sql,list);
        if(lst.size() > 0)
            return (Map)lst.get(0);
        else
            return null;
    }

    /**
     * 根据SQL名称查询一个记录
     * @param sqlName SQL名称
     * @param list SQL语句需要的参数值
     * @return 返回一个对象
     */
    @SuppressWarnings("unchecked")
    public Map queryMapBySQLName(String sqlName, Object[] list) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        sqlLog.debug("queryMapBySQLName", sqlName);
        return this.queryMapBySQL(sql, list);
    }


    /**
     * 根据SQL名称查询一个记录
     * @param sqlName SQL名称

     * @return 返回一个对象
     */
    public Map queryMapBySQLName(String sqlName, Map map, Map custom){
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);

        Map mapQuery = ColumnUtil.getQueryCondition(map);
        String condition = StringUtil.getTrim(mapQuery.get("sql"));

        Map mapCustom = ColumnUtil.getQueryCustom(custom);


        sql = sql.replace("[custom]", StringUtil.getTrim(mapCustom.get("sql")));

        if(!condition.equals("")){
            condition = " 1=1 "+condition;
        }else{
            condition = " 1=1 ";
        }
        sql = sql.replace("[condition]", condition);



        List list = new ArrayList();
        list.addAll((List)mapQuery.get("list"));
        list.addAll((List)mapCustom.get("list"));


        return this.queryMapBySQL(sql, list.toArray());

    }

    /**
     * 根据SQL名称查询一个记录
     * @param sqlName SQL名称
     * @param list SQL语句需要的参数值
     * @return 返回一个对象
     */
    @SuppressWarnings("unchecked")
    public Map queryMapBySQLNameReplace(String sqlName, String replaceStr, Object[] list) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName).replace("[condition]", replaceStr);
        sqlLog.debug("queryMapBySQLNameReplace", sqlName, replaceStr);
        return this.queryMapBySQL(sql, list);
    }

    /**
     * 根据主键查询一个对象
     * @param tableName 表名
     * @param id 主键ID
     * @return 返回一个对象
     */
    @SuppressWarnings("unchecked")
    public Map queryMap(String tableName,Object id) {
        return queryMap(tableName,new Object[]{},new Object[]{ColumnUtil.getPrimaryKey(tableName)},new Object[]{id},"=");
    }
    ///////////////////////////////////////查询一个对象 map//////////////////////////////////

    /**
     * 查询一个表的记录总数
     * @param tableName 表名
     * @param properties 属性名数组
     * @param values 属性值数组
     * @param oper =或like
     * @return 返回统计的总数
     */
    public long queryCount(String tableName,Object[] properties,Object[] values,String oper){
        try{
            if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//云平台框架自动添加公司id

                Object[] newProperties = new Object[properties.length + 1];
                System.arraycopy(properties,0,newProperties,0,properties.length);
                newProperties[newProperties.length-1] = "com_id";
                properties = newProperties;

                Object[] newValues = new Object[values.length + 1];
                System.arraycopy(values,0,newValues,0,values.length);
                newValues[newValues.length-1] = getComId();
                values = newValues;
            }

            String sql = "select count(*) from " + tableName + " where is_delete = '0' ";
            int len = properties.length;
            for (int index = 0; index < len; index++) {
                if ("like".equalsIgnoreCase(oper) && !StringUtil.getTrim(values[index]).contains("%")) {
                    sql += " and " + properties[index] + " = ? ";
                } else {
                    sql += " and " + properties[index] + " " + oper + " ? ";
                }
            }
            sqlLog.debug("queryCount",sql,values);
            return getJdbcTemplate().queryForObject(sql, values, Long.class);
        }catch(Exception e){
            log.error("", e);
            return 0;
        }
    }

    /**
     * 根据SQL语句查询一个表的记录总数
     * @param sql SQL语句
     * @param list SQL语句查询条件的参数值
     * @return 返回统计的总数
     */
    public long queryCountBySQL(String sql, Object[] list) {
        sqlLog.debug("queryCountBySQL",sql,list);
        return getJdbcTemplate().queryForObject(sql, list, Long.class);
    }

    /**
     * 根据SQL名称一个表的记录总数
     * @param sqlName SQL名称
     * @param list SQL语句查询条件的参数值
     * @return 返回统计的总数
     */
    public long queryCountBySQLName(String sqlName, Object[] list) {

        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);

        return this.queryCountBySQL(sql, list);
    }

    /**
     * 根据SQL名称一个表的记录总数
     * @param sqlName SQL名称
     * @param replaceStr SQL语句中替换[condition]
     * @param list SQL语句查询条件的参数值
     * @return
     */
    public long queryCountBySQLNameReplace(String sqlName, String replaceStr, Object[] list) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName).replace("[condition]", replaceStr);
        return this.queryCountBySQL(sql, list);
    }

    /**
     * 分页查询
     * @param tableName 表名称
     * @param page 分页对象
     * @param columns 查询列名
     * @param map 查询条件
     * @param custom 自定义条件
     * @return
     */
    @SuppressWarnings("unchecked")
    public Page queryPage(String tableName, Page page, Object[] columns, Map map, Map custom) {

        if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//自动添加查询条件com_id=xxx
            map.put("com_id", getComId());
        }

        Map mapCustom = ColumnUtil.getQueryCustom(custom);
        String customSql = StringUtil.getTrim(mapCustom.get("sql"));

        String sql = "select * from " + tableName + " where is_delete = '0' ";
        String sqlAll = "select count(*) from " + tableName + " where is_delete = '0' ";
        if (columns.length > 0)
            sql = "select " + ColumnUtil.getColumns(columns) + " from " + tableName + " where is_delete = '0' ";

        if(!customSql.equals("")) {
            sql = sql + " " + customSql;
            sqlAll = sqlAll +" "+customSql;
        }

        Map mapQuery = ColumnUtil.getQueryCondition(map);
        sql += StringUtil.getTrim(mapQuery.get("sql"));
        sqlAll += StringUtil.getTrim(mapQuery.get("sql"));


        if (map.get("orderby") != null) {
            String orderBy = map.get("orderby").toString();
            if(ColumnUtil.isSqlPropertyNameValid(orderBy)) {
                sql = sql + "  order by " + orderBy + " ";
            }
        }

        int start = page.getPageNo();
        int limit = page.getPageSize();
        sql = sql + " limit ? , ? ";

        List list = new ArrayList();
        list.addAll((List)mapCustom.get("list"));
        list.addAll((List)mapQuery.get("list"));
        list.add((start - 1) * limit);
        list.add(limit);

        sqlLog.debug("queryPage",sql,list);
        List<Map<String, Object>> lst = getJdbcTemplate().queryForList(sql,list.toArray());
        page.setResult(lst);
        page.setPageNo(start);
        page.setPageSize(limit);

        List countList = new ArrayList();
        countList.addAll((List)mapCustom.get("list"));
        countList.addAll((List)mapQuery.get("list"));

        sqlLog.debug("queryPage",sqlAll,countList);
        page.setTotalCount(getJdbcTemplate().queryForObject(sqlAll,countList.toArray(), Long.class));
        //page.setMap(map);
        return page;
    }

    /**
     * 通过分页条件查询列表
     * @param tableName 表名称
     * @param columns 查询列名
     * @param map 查询条件
     * @param custom 自定义条件
     * @return
     */
    @SuppressWarnings("unchecked")
    public List queryList(String tableName,Object[] columns,Map map,Map custom) {

        if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//自动添加查询条件com_id=xxx
            map.put("com_id", getComId());
        }

        Map mapCustom = ColumnUtil.getQueryCustom(custom);
        String customSql = StringUtil.getTrim(mapCustom.get("sql"));

        if(customSql.equals(""))
            customSql = " 1=1 ";
        String sql = "select * from " + tableName + " where is_delete = '0' and "+customSql;
        if(columns.length > 0)
            sql = "select " + ColumnUtil.getColumns(columns) + " from "+ tableName + " where is_delete = '0' and "+customSql;


        Map mapQuery = ColumnUtil.getQueryCondition(map);
        sql += StringUtil.getTrim(mapQuery.get("sql"));

        if (map.get("orderby") != null) {
            String orderBy = map.get("orderby").toString();
            if(ColumnUtil.isSqlPropertyNameValid(orderBy)) {
                sql = sql + "  order by " + orderBy + " ";
            }
        }

        List list = new ArrayList();
        list.addAll((List)mapCustom.get("list"));
        list.addAll((List)mapQuery.get("list"));

        sqlLog.debug("queryList",sql,list);
        List<Map<String, Object>> lst = getJdbcTemplate().queryForList(sql,list.toArray());

        return lst;
    }

    /**
     * 通过分页条件查询列表
     * @param tableName 表名称
     * @param columns 查询列名
     * @param map 查询条件
     * @param custom 自定义条件
     * @return
     */
    @SuppressWarnings("unchecked")
    public List queryList(String tableName,Object[] columns,Map map,Map custom, int start, int limit) {

        if(isNeedComId(tableName) && isNeedSelectComId(tableName)){//自动添加查询条件com_id=xxx
            map.put("com_id", getComId());
        }

        Map mapCustom = ColumnUtil.getQueryCustom(custom);
        String customSql = StringUtil.getTrim(mapCustom.get("sql"));

        if(customSql.equals(""))
            customSql = " 1=1 ";
        String sql = "select * from " + tableName + " where is_delete = '0' and "+customSql;
        if(columns.length > 0)
            sql = "select " + ColumnUtil.getColumns(columns) + " from "+ tableName + " where is_delete = '0' and "+customSql;


        Map mapQuery = ColumnUtil.getQueryCondition(map);
        sql += StringUtil.getTrim(mapQuery.get("sql"));

        if (map.get("orderby") != null) {
            String orderBy = map.get("orderby").toString();
            if(ColumnUtil.isSqlPropertyNameValid(orderBy)) {
                sql = sql + "  order by " + orderBy + " ";
            }
        }

        sql = sql + " limit ? , ? ";

        List list = new ArrayList();
        list.addAll((List)mapCustom.get("list"));
        list.addAll((List)mapQuery.get("list"));
        list.add(start);
        list.add(limit);

        sqlLog.debug("queryList",sql,list);
        List<Map<String, Object>> lst = getJdbcTemplate().queryForList(sql,list.toArray());

        return lst;
    }

    /**
     * 分页查询
     * @param tableName 表名称
     * @param page 分页对象
     * @param map 查询条件
     * @return
     */
    @SuppressWarnings("unchecked")
    public Page queryPage(String tableName,Page page,Map map) {
        return queryPage(tableName,page,new Object[]{},map,new Hashtable());
    }

    /**
     * 根据SQL名称分页查询
     * @param page 分页的对象
     * @param sqlName 查询分页的SQL名称
     * @param sumSqlName 查询总数的SQL名称
     * @param map 查询条件
     * @param custom 自定义条件
     * @return 返回page对象
     */
    @SuppressWarnings("unchecked")
    public Page querySQLPage(Page page,String sqlName,String sumSqlName,Map map,Map custom) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        String sumSQL = SystemSQL.getInstance().getSystemSQL(sumSqlName);

        Map mapQuery = ColumnUtil.getQueryCondition(map);
        String condition = StringUtil.getTrim(mapQuery.get("sql"));

        Map mapCustom = ColumnUtil.getQueryCustom(custom);



        sql = sql.replace("[custom]", StringUtil.getTrim(mapCustom.get("sql")));
        sumSQL = sumSQL.replace("[custom]", StringUtil.getTrim(mapCustom.get("sql")));

        if(!condition.equals("")){
            condition = " 1=1 "+condition;
        }else{
            condition = " 1=1 ";
        }
        sql = sql.replace("[condition]", condition);
        sumSQL = sumSQL.replace("[condition]", condition);


        if (StringUtil.getTrim(map.get("orderby")).length() > 0) {
            sql = sql.replace("[orderby]", " order by "+StringUtil.getTrim(map.get("orderby"))+" ");
        }else{
            sql = sql.replace("[orderby]","");
        }


        int start = page.getPageNo();
        int limit = page.getPageSize();

        List list = new ArrayList();
        list.addAll((List)mapQuery.get("list"));
        list.addAll((List)mapCustom.get("list"));
        list.add((start - 1) * limit);
        list.add(limit);

        sqlLog.debug("querySQLPage",sql,list);

        List<Map<String, Object>> lst = getJdbcTemplate().queryForList(sql,list.toArray());
        page.setResult(lst);
        page.setPageNo(start);
        page.setPageSize(limit);

        List countList = new ArrayList();
        countList.addAll((List)mapQuery.get("list"));
        countList.addAll((List)mapCustom.get("list"));

        sqlLog.debug("querySQLPage",sumSQL,countList);
        page.setTotalCount(getJdbcTemplate().queryForObject(sumSQL,countList.toArray(), Long.class));
        //page.setMap(map);
        return page;
    }

    /**
     * 根据SQL名称分页查询
     * @param page 分页的对象
     * @param sqlName 查询分页的SQL名称
     * @param sumSqlName 查询总数的SQL名称
     * @param map 查询条件
     * @param custom 自定义字符串查询条件
     * @param list SQL的查询条件参数
     * @return 返回page对象
     */
    @SuppressWarnings("unchecked")
    public Page querySQLPageValues(Page page,String sqlName,String sumSqlName,Map map,Map custom,List list) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        String sumSQL = SystemSQL.getInstance().getSystemSQL(sumSqlName);

        Map mapQuery = ColumnUtil.getQueryCondition(map);
        String condition = StringUtil.getTrim(mapQuery.get("sql"));

        Map mapCustom = ColumnUtil.getQueryCustom(custom);



        sql = sql.replace("[custom]", StringUtil.getTrim(mapCustom.get("sql")));
        sumSQL = sumSQL.replace("[custom]", StringUtil.getTrim(mapCustom.get("sql")));

        if(!condition.equals("")){
            condition = " 1=1 "+condition;
        }else{
            condition = " 1=1 ";
        }
        sql = sql.replace("[condition]", condition);
        sumSQL = sumSQL.replace("[condition]", condition);

        if (StringUtil.getTrim(map.get("orderby")).length() > 0) {
            sql = sql.replace("[orderby]", " order by "+StringUtil.getTrim(map.get("orderby"))+" ");
        }else{
            sql = sql.replace("[orderby]","");
        }

        list.addAll((List)mapQuery.get("list"));
        list.addAll((List)mapCustom.get("list"));

        sqlLog.debug("querySQLPageValues",sumSQL,list);

        long count = getJdbcTemplate().queryForObject(sumSQL, list.toArray(), Long.class);

        int start = page.getPageNo();
        int limit = page.getPageSize();
        list.add((start - 1) * limit);
        list.add(limit);

        sqlLog.debug("querySQLPageValues",sql,list);

        List<Map<String, Object>> lst = getJdbcTemplate().queryForList(sql,list.toArray());
        page.setResult(lst);
        page.setPageNo(start);
        page.setPageSize(limit);
        page.setTotalCount(count);
        //page.setMap(map);
        return page;
    }

    /**
     * 根据SQL名称分页查询
     * @param page 分页的对象
     * @param sqlName 查询分页的SQL名称
     * @param sumSqlName 查询总数的SQL名称
     * @param list SQL的查询条件参数
     * @return 返回page对象
     */
    @SuppressWarnings("unchecked")
    public Page queryPageSQLName(Page page,String sqlName,String sumSqlName,List<Object> list) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        String sumSQL = SystemSQL.getInstance().getSystemSQL(sumSqlName);
        return this.queryPageSQL(page,sql,sumSQL,list);
    }

    /**
     * 根据SQL名称分页查询，并替换[condition]条件
     * @param page 分页的对象
     * @param sqlName 查询分页的SQL名称
     * @param sumSqlName 查询总数的SQL名称
     * @param conditionStr 替换[condition]的条件字符串
     * @param list SQL的查询条件参数
     * @return 返回page对象
     */
    @SuppressWarnings("unchecked")
    public Page queryPageSQLNameReplace(Page page,String sqlName,String sumSqlName,String conditionStr,List<Object> list) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName).replace("[condition]", conditionStr);
        String sumSQL = SystemSQL.getInstance().getSystemSQL(sumSqlName).replace("[condition]", conditionStr);
        return this.queryPageSQL(page,sql,sumSQL,list);
    }

    /**
     * 根据SQL名称分页查询
     * @param page 分页的对象
     * @param sqlName 查询分页的SQL名称
     * @param sumSqlName 查询总数的SQL名称
     * @param listSQL 列表查询条件参数
     * @param listSUM 总条数查询条件参数
     * @return 返回page对象
     */
    @SuppressWarnings("unchecked")
    public Page queryPageSQLName(Page page,String sqlName,String sumSqlName,List<Object> listSQL,List<Object> listSUM) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        String sumSQL = SystemSQL.getInstance().getSystemSQL(sumSqlName);
        return this.queryPageSQL(page,sql,sumSQL,listSQL,listSUM);
    }

    /**
     * 根据SQL分页查询
     * @param page 分页的对象
     * @param sql  查询分页的SQL语句
     * @param sumSQL 查询总数的SQL语句
     * @param list SQL的查询条件参数
     * @return 返回page对象
     */
    @SuppressWarnings("unchecked")
    public Page queryPageSQL(Page page,String sql,String sumSQL,List<Object> list) {

        sqlLog.debug("queryPageSQL",sumSQL,list);
        long count = getJdbcTemplate().queryForObject(sumSQL, list.toArray(), Long.class);
        int start = page.getPageNo();
        int limit = page.getPageSize();
        list.add((start - 1) * limit);
        list.add(limit);

        sqlLog.debug("queryPageSQL",sumSQL,list);
        List<Map<String, Object>> lst = getJdbcTemplate().queryForList(sql,list.toArray());
        page.setResult(lst);
        page.setPageNo(start);
        page.setPageSize(limit);
        page.setTotalCount(count);
        return page;
    }

    /**
     * 根据SQL分页查询
     * @param page 分页的对象
     * @param sql  查询分页的SQL语句
     * @param sumSQL 查询总数的SQL语句
     * @param listSQL 查询列表条件参数
     * @param listSUM 查询总条数条件参数
     * @return 返回page对象
     */
    @SuppressWarnings("unchecked")
    public Page queryPageSQL(Page page,String sql,String sumSQL,List<Object> listSQL,List<Object> listSUM) {

        sqlLog.debug("queryPageSQL",sql,listSQL);

        long count = getJdbcTemplate().queryForObject(sumSQL, listSUM.toArray(), Long.class);
        int start = page.getPageNo();
        int limit = page.getPageSize();
        listSQL.add((start - 1) * limit);
        listSQL.add(limit);

        sqlLog.debug("queryPageSQL",sumSQL,listSUM);

        List<Map<String, Object>> lst = getJdbcTemplate().queryForList(sql,listSQL.toArray());
        page.setResult(lst);
        page.setPageNo(start);
        page.setPageSize(limit);
        page.setTotalCount(count);
        return page;
    }

    /**
     * 查询一个表的自动编号
     * @param tableName 表名
     * @param column 编号的列名
     * @param property 查询的属性名称
     * @param prefix 查询属性的前缀
     * @param bit 指定返回编号的位数
     * @return 返回一个最大的编号
     */
    @SuppressWarnings("unchecked")
    public String getMaxCode(String tableName,String column,String property,String prefix,int bit){
        String sql = "select "+column+" from "+tableName+" where "+property+" = '"+prefix+"' order by "+column+" desc limit 0,1";

        if(isNeedComId(tableName) && isNeedSelectComId(tableName)) {//自动添加查询条件com_id=xxx
            sql = "select "+column+" from "+tableName+" where "+property+" = '"+prefix+"' and com_id = '"+getComId()+"' order by "+column+" desc limit 0,1";
        }

        List list = this.getJdbcTemplate().queryForList(sql,new Object[]{});
        if(list == null || list.size() == 0)
            return prefix+fillZero(bit-1)+1;
        Map entity = (Map)list.get(0);
        String strCode = entity.get(column).toString();
        strCode = strCode.substring(prefix.length());
        int code = Integer.parseInt(strCode);
        code ++;
        String returnCode = "";
        for(int index = 1;index <= bit;index ++){
            if(code < Math.pow(10,index)){
                returnCode = prefix+fillZero(bit - index)+code;
                break;
            }
        }
        return returnCode;
    }

    /**
     * 查询一个表的自动编号
     * @param tableName 表名
     * @param column 编号的列名
     * @param prefix 查询属性的前缀
     * @param bit 指定返回编号的位数
     * @return 返回一个最大的编号
     */
    @SuppressWarnings("unchecked")
    public String getMaxCode(String tableName,String column,String prefix,int bit){
        String sql = "select "+column+" from "+tableName+" where "+column+" like '"+prefix+"%' order by "+column+" desc limit 0,1";

        if(isNeedComId(tableName) && isNeedSelectComId(tableName)) {//自动添加查询条件com_id=xxx
            sql = "select "+column+" from "+tableName+" where "+column+" like '"+prefix+"%" +
                    "' and com_id = '"+getComId()+"' order by "+column+" desc limit 0,1";
        }

        sqlLog.debug("getMaxCode",sql,new Object[]{})
        ;
        List list = this.getJdbcTemplate().queryForList(sql,new Object[]{});
        if(list == null || list.size() == 0)
            return prefix+fillZero(bit-1)+1;
        Map entity = (Map)list.get(0);
        String strCode = entity.get(column).toString();
        strCode = strCode.substring(prefix.length());
        int code = Integer.parseInt(strCode);
        code ++;
        String returnCode = "";
        for(int index = 1;index <= bit;index ++){
            if(code < Math.pow(10,index)){
                returnCode = prefix+fillZero(bit - index)+code;
                break;
            }
        }
        return returnCode;
    }

    /**
     * 补充指定数目的0
     * @param num 补充零的个数
     * @return
     */
    private String fillZero(int num){
        String fill = "";
        for(int index = 0;index < num;index ++){
            fill += "0";
        }
        return fill;
    }

}
